#include "Database.h"
Database::Database(){
  db=NULL;
  char  dbName[]="assn2.db";
  open(dbName);
  fail=0;
}

Database::~Database(){

}

sqlite3* Database::getSqlite3(){
	return db;
}

void Database::open(char * filename){
  int rc = sqlite3_open(filename, &db);
  
  if(rc){
    cout << "Can't open database\n";
    fail = 1;
  } 
  else {
   // cout << "Open database successfully\n\n";
  } 

}

int Database::getFail(){
	return fail;
}

void Database::close(){
        sqlite3_close(db);   
}

vector<vector<string> > Database::query(string statement){
/**
*  Short story : throw your sql statement into this function and it will return results
*  
*	read the following to understand
*/

  sqlite3_stmt* res; // this is sqlite's results output pointer
  const char* tail; // i have no idea what this does actually.
  
  // one row contains many outputs (e.g id, firstname, lastname)  therefore vector<string> 
  // there might be more than 1 row everytime you execute therefore you need a listOfRows
  vector<vector<string> > listOfRows;

  // the actual execution of the statement. returns a number for validation
 int error = sqlite3_prepare_v2(db, statement.c_str(), statement.length(), &res, &tail);  
 
  if (error != SQLITE_OK){ // if unable to execute statements
    cout << "Unable to prepare statement!" <<endl;
    sqlite3_close(db);
  }
  else{ // if execution okay

    int cols = sqlite3_column_count(res); // count the number of columns that the statement will output

	while (sqlite3_step(res) == SQLITE_ROW){ // for every row of results
		// for every row of results, redeclare resultsInOneRow. (to wipe clean).
		vector<string> resultsInOneRow; 
		
		for (int i=0;i<cols;i++){ // start of reading the output
								  // using cols to tell the program how many columns in one row
			
			// cout << sqlite3_column_text(res,i) <<endl; // display results in one column
			
			// results is in const unsigned char*. re-cast it to string
			string temp = string(reinterpret_cast<const char*>(sqlite3_column_text(res,i))); 
			
			// push that string into a vector.
			resultsInOneRow.push_back(temp);
		
		}// end of reading the results in one row

		// push the row into a list of rows.
		listOfRows.push_back(resultsInOneRow);

    
	 }// end of reading every row
	 
	 sqlite3_finalize(res); // clear statement.
  }// end else prepare statement is ok

  // return the listOfRows
  return listOfRows;
}

bool Database::checkForUser(string usernameInput){
  vector<vector<string> >listOfAccounts = query("SELECT username FROM `USERS` WHERE username = '"+ usernameInput +"';");
  if (listOfAccounts.size() == 0)
    return false;
  return true;
}

bool Database::checkForStudent(string usernameInput){
  vector<vector<string> >listOfAccounts = query("SELECT fullname FROM `STUDENTS` WHERE fullname = '" +usernameInput+ "';");
  if (listOfAccounts.size() == 0)
    return false;
  return true;
}
bool Database::checkForAdmin(string usernameInput){
  vector<vector<string> >listOfAccounts = query("SELECT fullname FROM `ADMINS` WHERE fullname = '" +usernameInput+ "';");
  if (listOfAccounts.size() == 0)
    return false;
  return true;
}
bool Database::checkForLecturer(string usernameInput){
  vector<vector<string> >listOfAccounts = query("SELECT fullname FROM `LECTURERS` WHERE fullname = '" +usernameInput+ "';");
  if (listOfAccounts.size() == 0)
    return false;
  return true;
}
vector<vector<string> > Database::returnModuleOfLesson(string lessonIdInput){
  return (query("SELECT module_code FROM LESSONS WHERE lessons_id = '"+lessonIdInput+"';"));
}

vector<vector<string> > Database::returnModuleFromLM(string fullNameInput){
  vector<vector<string> >listOfModules = query("SELECT module_code FROM LECTURERS_MODULES WHERE fullName = '"+fullNameInput+"';");
  return listOfModules;
}

int Database::authenticate(string usernameInput, string passwordInput){
  if (checkLockedUser(usernameInput)){
    return 2;
  }

  vector<vector<string> > listOfAccounts;
  // listOfAccounts = db.query("SELECT username, password, type, status, fullname, email, tries FROM `USERS`;");
  listOfAccounts = query("SELECT password FROM `USERS` WHERE username = '"+ usernameInput  + "';");
  
  if(listOfAccounts.size() == 0)
    return -1;

  vector<string> temp =listOfAccounts[0];
    string password = temp[0];

  bool passwordMatched = (password == passwordInput);

  if (passwordMatched){
    resetTries(usernameInput);
    populate(usernameInput);
    return 0;
  }else{
    bool userIsLocked = (increaseTries(usernameInput) == 2);
    if (userIsLocked)
      return 2;
    else 
      return 1;
  }
  
}

void Database::populate(string usernameInput){
  vector<vector<string> > listOfAccounts;
  listOfAccounts = query("SELECT username, type, fullname, email FROM `USERS` WHERE username = '" + usernameInput + "';");
  if (listOfAccounts.size() == 1 ){
    vector<string> temp =listOfAccounts[0];
    username = temp[0];
    type = temp[1];
    fullName = temp[2];
    email = temp[3];
  }
    

}
void Database::resetTries(string usernameInput){
  query("UPDATE `USERS` SET tries = 0 WHERE username = '" + usernameInput +"';");
}

void Database::lockUser(string usernameInput){
  string lockStatement = "UPDATE `USERS` SET status = 'LOCKED' WHERE username = '" + usernameInput +"';";
  query(lockStatement);
}

int Database::increaseTries(string usernameInput){
  string statement = "UPDATE `USERS` SET tries = tries+1 WHERE username = '" + usernameInput +"';";
  query(statement);
  vector<vector<string> > listOfAccounts;
  listOfAccounts = query("SELECT tries FROM `USERS` WHERE username = '"+ usernameInput  + "';");
  
  if (listOfAccounts.size() != 1)
    return -1;
  vector<string> temp =listOfAccounts[0];
  int tries;
  stringstream ss;
  ss << temp[0];
  ss >> tries;
  if(tries >= 3){
    lockUser(usernameInput);
    return 2;
  }
}

void Database::changePassword(string passwordInput){

  string statement = "UPDATE `USERS` SET password = '" + passwordInput + "' WHERE username = '" + username + "' ;";
  query(statement);

}
void Database::unlockUser(string usernameInput){
  resetTries(usernameInput);
  query("UPDATE `USERS` SET status = 'OK' WHERE username = '" +usernameInput +"';");
}


bool Database::checkLockedUser(string usernameInput){
  string statement = "SELECT status FROM `USERS` WHERE username = '" + usernameInput + "' AND status = 'LOCKED';";
  vector<vector<string> > listOfAccounts = query(statement);

  if (listOfAccounts.size() == 0)
    return false;
  return true;
}

bool Database::checkForLessonsId(string lessonID)
{
    vector<vector<string > > tempID = query("select lessons_id from LESSONS where lessons_id = '"+lessonID+"';");
    if (tempID.size() == 0)
        return false;
    return true;
}
            
bool Database::checkModuleCode(string moduleCodeInput){//SELECT * FROM MODULES WHERE module_code = 'CSCItesting'
  vector<vector<string> > listOfModules = query("SELECT * FROM MODULES WHERE module_code = '"+moduleCodeInput+"';");
  if (listOfModules.size() == 0)
    return false;
  return true;
}

vector<vector<string> > Database::returnPtId(string placeInput, string timeslotInput){
  vector<vector<string> > listOfPtId = query ("SELECT pt_id FROM PLACE_TIME WHERE strftime ('%Y-%m-%d %H:%M', timeslot) = '"+timeslotInput+"';");
  return (listOfPtId);
}

bool Database::checkForVenue(string venueInput){
  vector<vector <string> > listOfVenues = query ("SELECT venue FROM PLACE_TIME WHERE venue = '"+venueInput+"'");
  if (listOfVenues.size() ==  0)
    return false;
  return true;
}

bool Database::checkForTimeslot(string timeslotInput){
  vector<vector <string> > listOfTimeslot = query ("SELECT pt_id FROM PLACE_TIME WHERE strftime ('%Y-%m-%d %H:%M', timeslot) = '"+timeslotInput+"';");
  if (listOfTimeslot.size() == 0)
    return false;
  return true;
}



string Database::getUsername(){  
  return username;
}
string Database::getFullName(){
  return fullName;
}
string Database::getEmail(){
  return email;
}

string Database::getType(){
  return type;
}